# coding: utf-8
# author: yopoing
import datetime
import MysqlUtils
import Config
import os

# 当前检查执行时间
t_list = ['2017-01-01', '2017-01-02', '2017-01-03', '2017-01-04', '2017-01-05', '2017-01-06',
          '2017-01-07', '2017-01-08', '2017-01-09', '2017-01-10', '2017-01-11', '2017-01-12',
          '2017-01-13', '2017-01-14', '2017-01-15', '2017-01-16', '2017-01-17', '2017-01-18']
db = MysqlUtils.MysqlHelper()
for t in t_list:
    # 检查上一日是否已经有该流量计的统计信息，没有的话才统计并加入到表格。
    sql = """
        select fr.flowmeter_name, t2.flowmeter_type, CONCAT(t2.flowmeter_temperature,'(',t1.flowmeter_temperature,')'), CONCAT(t2.flowmeter_pressure,'(',t1.flowmeter_pressure,')'), CONCAT(t2.flowmeter_g,'(',t1.flowmeter_g,')'),
                CONCAT(t2.flowmeter_b,'(',t1.flowmeter_b,')'), CONCAT(t2.flowmeter_total_g,'(',t1.flowmeter_total_g,')'), CONCAT(t2.flowmeter_total_b,'(',t1.flowmeter_total_b,')'),
                (t2.flowmeter_total_g-t1.flowmeter_total_g) as g_ryq, (t2.flowmeter_total_b-t1.flowmeter_total_b) as b_ryq,
                 CONCAT(date_format(t2.add_time,'%%Y-%%m-%%d %%H:%%i:%%s'),'(',date_format(t1.add_time,'%%Y-%%m-%%d %%H:%%i:%%s'),')'), t2.flowmeter_status, cr.name as cr_name, s.name as sname, c.name as cname, cr.cno, fr.flowmeter_id, fr.id, fr.flowmeter_spec from
    (select * from (select * from natgas_flowmeterdata WHERE DATE_FORMAT(add_time,'%%Y-%%m-%%d')=date_sub('%s', interval 1 day) AND HOUR(add_time)<=7 order by id desc) as a group by flowmeter_id) as t1,
    (select * from (select * from natgas_flowmeterdata WHERE DATE_FORMAT(add_time,'%%Y-%%m-%%d')='%s' AND HOUR(add_time)<=7 order by id desc) as a group by flowmeter_id) as t2,
    natgas_flowmeterreg fr,natgas_concentratorreg cr,natgas_site s,natgas_company c
    where t1.flowmeter_id=t2.flowmeter_id and t2.flowmeter_id=fr.id and fr.cno_id=cr.id
    and cr.site_id=s.id and s.company_id=c.id
        """ % (t, t)
    print 'sql:', sql
    result = db.query(sql)
    # 根据已有数据插入统计数据
    for r in result:
        # 查询是否已经有上一日的统计数据,没有的话则插入
        insert_sql = """
            INSERT INTO natgas_flowmeterdatadaytotal(flowmeter_g, flowmeter_b, add_time, flowmeter_id)
            SELECT '%s', '%s', '%s', '%s' FROM DUAL WHERE not exists (select * from natgas_flowmeterdatadaytotal
            WHERE flowmeter_id = '%s' AND DATE_FORMAT(add_time,'%%Y-%%m-%%d') = '%s')
            """ % (r['g_ryq'], r['b_ryq'], t, r['id'], r['id'], t)
        print 'insert_sql:', insert_sql
        db.dml(insert_sql)
db.close()